﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace DapperDemo
{
    class Program
    {
        private static readonly string CONN_STRING = "server=.;uid=sa;pwd=123456;database=student";
        private static readonly string SQL_STRING = "select * from Test where WorkFlowId = @WorkFlowId and WorkCenterId = @WorkCenterId and DateTime >= @Start and DateTime <@End";
        private static readonly string INSERT_STRING = "insert into Test(WorkFlowId, WorkCenterId, DateTime) values(@WorkFlowId, @WorkCenterId, @DateTime)";

        static void Main(string[] args)
        {
            var stopWatch = new Stopwatch();
            stopWatch.Start();

            var model = new TestClass()
            {
                WorkFlowId = "A10356987412",
                WorkCenterId = "A10236985214",
            };
            var start = DateTime.Parse("2018-05-01 10:00:00");
            var end = DateTime.Parse("2018-06-20 10:00:00");


            // 监测普通写法执行时间
            stopWatch.Restart();
            var list = GetSimpleTestList(model, start, end);
            stopWatch.Stop();

            Console.WriteLine("执行GetSimpleTestList()方法返回计算结果条数：{0}，用时{1}毫秒", list.Count, stopWatch.ElapsedMilliseconds);

            // 监测使用正确类型及长度写法执行时间
            stopWatch.Restart();
            list = GetMatchTestList(model, start, end);
            stopWatch.Stop();
            Console.WriteLine("执行GetMatchTestList()方法返回计算结果条数：{0}，用时{1}毫秒", list.Count, stopWatch.ElapsedMilliseconds);

            Console.ReadKey();
        }
        private static List<TestClass> GetSimpleTestList(TestClass model, DateTime start, DateTime end)
        {
            var param = new DynamicParameters(new
            {
                model.WorkFlowId,
                model.WorkCenterId,
                Start = start,
                End = end
            });

            var list = new List<TestClass>();
            using (var conn = new SqlConnection(CONN_STRING))
            {
                var query = conn.Query<TestClass>(SQL_STRING, param);
                if (null != query) list = query.AsList();
            }

            return list;
        }

        private static List<TestClass> GetMatchTestList(TestClass model, DateTime start, DateTime end)
        {
            var param = new DynamicParameters();
            // 说明：db中是char(12)故使用DbType.AnsiStringFixedLength,长度12，而不是使用DbType.String这种(这个是varchar&nvarchar
            // 同时长度12则与db中完全一致(char(12)),避免使用其他类型或者长度，而导致的sql server解析的时候还需要做Cast类型转换,引起很大的性能损耗
            param.Add("WorkFlowId", model.WorkFlowId, DbType.AnsiStringFixedLength, ParameterDirection.Input, 12);
            param.Add("WorkCenterId", model.WorkCenterId, DbType.AnsiStringFixedLength, ParameterDirection.Input, 12);
            // 同理，如果直接用 Start = start,或者 param.Add("Start", start)则会根据传入的时间是2018-06-01转为Date,是2018-06-01 10:00:00转为DateTime2
            // 这样在db中执行查询的时候还需要将Date | DateTime2作Cast转换为DateTime,同样有很大性能损耗)
            param.Add("Start", start, DbType.DateTime);
            param.Add("End", end, DbType.DateTime);

            var list = new List<TestClass>();
            using (var conn = new SqlConnection(CONN_STRING))
            {
                var query = conn.Query<TestClass>(SQL_STRING, param);
                if (null != query) list = query.AsList();
            }

            return list;
        }

        private static bool Insert<T>(List<T> list)
        {
            using (var conn = new SqlConnection(CONN_STRING))
            {
                return conn.Execute(INSERT_STRING, list) > 0;
            }
        }
    }

    class TestClass
    {
        public int Id { get; set; }
        public string WorkFlowId { get; set; }
        public string WorkCenterId { get; set; }
        public DateTime DateTime { get; set; }
    }
}